﻿using EntityFramework.Extensions;
using EntityFramework.Mapping;
using Fasterflect;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Linq;
using System.Text;

namespace HobSoft.SoccerBet2014.Common.DB.Queryable
{
    using Common.Queryable;
    public static class DbExtensions
    {
        public static IQueryable<TEntity> Sort<TEntity>(this IQueryable<TEntity> query, params ISortExpression<TEntity>[] sortExpressions)
            where TEntity : class
        {
            if (sortExpressions == null)
                return query;

            IOrderedQueryable<TEntity> output = null;

            foreach (var orderByExpression in sortExpressions)
            {
                if (output == null)
                    output = orderByExpression.ApplyOrderBy(query);
                else
                    output = orderByExpression.ApplyThenBy(output);
            }

            return output ?? query;
        }

        private static string CreateInsertSql(EntityMap map)
        {
            StringBuilder insertSqlBuilder = new StringBuilder();
            StringBuilder nameSqlBuilder = new StringBuilder();
            StringBuilder valueSqlBuilder = new StringBuilder();
            bool insertNeedComma = false;
            insertSqlBuilder.AppendFormat("insert {0} (", map.TableName);
            foreach (var column in map.PropertyMaps)
            {
                if (insertNeedComma)
                {
                    nameSqlBuilder.AppendFormat(", [{0}]", column.ColumnName);
                    valueSqlBuilder.Append(", @p__");
                    valueSqlBuilder.Append(column.ColumnName);
                    valueSqlBuilder.Append("_{0}");
                }
                else
                {
                    nameSqlBuilder.AppendFormat("[{0}]", column.ColumnName);
                    valueSqlBuilder.Append("@p__");
                    valueSqlBuilder.Append(column.ColumnName);
                    valueSqlBuilder.Append("_{0}");
                    insertNeedComma = true;
                }
            }
            insertSqlBuilder.Append(nameSqlBuilder.ToString());
            insertSqlBuilder.Append(")");
            insertSqlBuilder.Append(" values(");
            insertSqlBuilder.Append(valueSqlBuilder.ToString());
            insertSqlBuilder.Append(");");
            return insertSqlBuilder.ToString();
        }

        private static string CreateUpdateSql(EntityMap map)
        {
            StringBuilder sqlUpdateBuilder = new StringBuilder();
            sqlUpdateBuilder.AppendFormat("update {0} set ", map.TableName);
            bool updateNeedComma = false;
            foreach (var column in map.PropertyMaps)
            {
                var keyQuery = map.KeyMaps.Where(k => k.ColumnName == column.ColumnName);
                if (keyQuery.Any()) continue;
                if (updateNeedComma)
                {
                    sqlUpdateBuilder.AppendFormat(", [{0}] = @p__{0}_", column.ColumnName);
                    sqlUpdateBuilder.Append("{0}");
                }
                else
                {
                    sqlUpdateBuilder.AppendFormat("[{0}] = @p__{0}_", column.ColumnName);
                    sqlUpdateBuilder.Append("{0}");
                    updateNeedComma = true;
                }
            }
            sqlUpdateBuilder.Append(" where ");
            bool needAnd = false;
            foreach (var key in map.KeyMaps)
            {
                if (needAnd)
                {
                    sqlUpdateBuilder.AppendFormat(" and [{0}] = @p__{0}_", key.ColumnName);
                    sqlUpdateBuilder.Append("{0}");
                }
                else
                {
                    sqlUpdateBuilder.AppendFormat(" [{0}] = @p__{0}_", key.ColumnName);
                    sqlUpdateBuilder.Append("{0}");
                    needAnd = true;
                }
            }
            return sqlUpdateBuilder.ToString();
        }

        private static string CreateExistsSql(EntityMap map)
        {
            StringBuilder sqlExists = new StringBuilder();
            sqlExists.AppendFormat("if exists(select * from {0} where ", map.TableName);
            bool needAnd = false;
            foreach (var key in map.KeyMaps)
            {
                if (needAnd)
                {
                    sqlExists.AppendFormat(" and [{0}] = @p__{0}_", key.ColumnName);
                    sqlExists.Append("{0}");
                }
                else
                {
                    sqlExists.AppendFormat(" [{0}] = @p__{0}_", key.ColumnName);
                    sqlExists.Append("{0}");
                    needAnd = true;
                }
            }
            sqlExists.Append(")");
            return sqlExists.ToString();
        }

        public static TEntity AddOrUpdate<TEntity>(this IDbSet<TEntity> dbSet, TEntity model)
            where TEntity : class
        {
            var objQuery = dbSet.ToObjectQuery();
            var objContext = objQuery.Context;
            var map = objQuery.GetEntityMap<TEntity>();
            EntityConnection entityConnection = objContext.Connection as EntityConnection;
            if (entityConnection == null)
                throw new ArgumentNullException("Connection NULL.");
            DbConnection updateConnection = entityConnection.StoreConnection;
            StringBuilder sqlBuilder = new StringBuilder();
            DbCommand updateCommand = updateConnection.CreateCommand();

            var insertSqlFormat = CreateInsertSql(map);
            var updateSqlFormat = CreateUpdateSql(map);
            var existsSqlFormat = CreateExistsSql(map);

            foreach (var column in map.PropertyMaps)
            {
                var paramter = updateCommand.CreateParameter();
                paramter.ParameterName = string.Format("@p__{0}_{1}", column.ColumnName, 0);
                var value = model.TryGetValue(column.PropertyName);
                if (value != null)
                    paramter.Value = value;
                else
                {
                    paramter.Value = DBNull.Value;
                    paramter.Size = 1;
                }
                updateCommand.Parameters.Add(paramter);
            }
            sqlBuilder.AppendFormat(existsSqlFormat, 0);
            sqlBuilder.AppendLine();
            sqlBuilder.AppendLine("begin");
            sqlBuilder.AppendFormat(updateSqlFormat, 0);
            sqlBuilder.AppendLine();
            sqlBuilder.AppendLine("end else begin");
            sqlBuilder.AppendFormat(insertSqlFormat, 0);
            sqlBuilder.AppendLine();
            sqlBuilder.AppendLine("end");

            try
            {
                if (updateConnection.State != System.Data.ConnectionState.Open)
                    updateConnection.Open();
                updateCommand.CommandText = sqlBuilder.ToString();
                if (objContext.CommandTimeout.HasValue)
                    updateCommand.CommandTimeout = objContext.CommandTimeout.Value;
                updateCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (updateConnection.State == System.Data.ConnectionState.Open)
                    updateConnection.Close();
            }

            return model;
        }

        public static int AddOrUpdate<TEntity>(this IDbSet<TEntity> dbSet, IList<TEntity> models)
            where TEntity : class
        {
            var objQuery = dbSet.ToObjectQuery();
            var objContext = objQuery.Context;
            var map = objQuery.GetEntityMap<TEntity>();
            EntityConnection entityConnection = objContext.Connection as EntityConnection;
            if (entityConnection == null)
                throw new ArgumentNullException("Connection NULL.");
            DbConnection updateConnection = entityConnection.StoreConnection;

            int maxRowCount = (int)Math.Floor((double)2099 / (double)map.PropertyMaps.Count);
            //maxRowCount = 2;
            int sqlCount = (int)Math.Ceiling((double)models.Count / (double)maxRowCount);
            StringBuilder[] sqlBuilder = new StringBuilder[sqlCount];
            DbCommand[] updateCommand = new DbCommand[sqlCount];
            sqlBuilder[0] = new StringBuilder();
            updateCommand[0] = updateConnection.CreateCommand();

            int rowIndex = 0;
            int sqlIndex = 0;
            var insertSqlFormat = CreateInsertSql(map);
            var updateSqlFormat = CreateUpdateSql(map);
            var existsSqlFormat = CreateExistsSql(map);
            foreach (var entity in models)
            {
                if (rowIndex >= maxRowCount)
                {
                    rowIndex = 0;
                    sqlIndex++;
                    sqlBuilder[sqlIndex] = new StringBuilder();
                    updateCommand[sqlIndex] = updateConnection.CreateCommand();
                }
                foreach (var column in map.PropertyMaps)
                {
                    var paramter = updateCommand[sqlIndex].CreateParameter();
                    paramter.ParameterName = string.Format("@p__{0}_{1}", column.ColumnName, rowIndex);
                    var value = entity.TryGetValue(column.PropertyName);
                    if (value != null)
                        paramter.Value = value;
                    else
                    {
                        paramter.Value = DBNull.Value;
                        paramter.Size = 1;
                    }
                    updateCommand[sqlIndex].Parameters.Add(paramter);
                }
                sqlBuilder[sqlIndex].AppendFormat(existsSqlFormat, rowIndex);
                sqlBuilder[sqlIndex].AppendLine();
                sqlBuilder[sqlIndex].AppendLine("begin");
                sqlBuilder[sqlIndex].AppendFormat(updateSqlFormat, rowIndex);
                sqlBuilder[sqlIndex].AppendLine();
                sqlBuilder[sqlIndex].AppendLine("end else begin");
                sqlBuilder[sqlIndex].AppendFormat(insertSqlFormat, rowIndex);
                sqlBuilder[sqlIndex].AppendLine();
                sqlBuilder[sqlIndex].AppendLine("end");
                rowIndex++;
            }

            int count = 0;
            try
            {
                if (updateConnection.State != System.Data.ConnectionState.Open)
                    updateConnection.Open();
                for (int i = 0; i < sqlCount; i++)
                {
                    updateCommand[i].CommandText = sqlBuilder[i].ToString();
                    if (objContext.CommandTimeout.HasValue)
                        updateCommand[i].CommandTimeout = objContext.CommandTimeout.Value;
                    count = +updateCommand[i].ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (updateConnection.State == System.Data.ConnectionState.Open)
                    updateConnection.Close();
            }
            return count;
        }
    }
}
